﻿Imports System
Imports MySql.Data.MySqlClient
Imports System.Collections
Imports GVariable.Variable

Public Class CDataProvider
#Region "Conector"
    Protected connection As MySqlConnection
    Protected strConn As String '= strVConn '= "Server=localhost;Database=qlbh;Uid=root;Pwd=123456"
    Protected adapter As MySqlDataAdapter
    Protected command As MySqlCommand
    Private strUID As String = uID
    Private strPWD As String = pWD
#End Region

    Public Sub MoKetNoi()
        Try
            strConn = strVConn
            connection = New MySqlConnection(strConn)
            connection.Open()
            intKiemTraKetNoi = 1
        Catch ex As Exception
            intKiemTraKetNoi = 0
        End Try
    End Sub

    Public Sub DongKetNoi()
        connection.Close()
        intKiemTraKetNoi = -1
    End Sub

    Public Function ExecuteScalar(ByVal strSelect As String) As String
        Dim strKQ As String = ""
        Try
            MoKetNoi()
            command = New MySqlCommand(strSelect, connection)
            command.CommandType = CommandType.Text
            strKQ = command.ExecuteScalar().ToString()
            intKTTruyVan = 1
            DongKetNoi()
        Catch ex As Exception
            intKTTruyVan = 0
        End Try
        Return strKQ
    End Function

    Public Sub ExecuteNonQuery(ByVal sQL As String)
        Try
            MoKetNoi()
            command = New MySqlCommand(sQL, connection)
            command.CommandType = CommandType.Text
            command.ExecuteNonQuery()
            DongKetNoi()
            intKTTruyVan = 1
        Catch ex As Exception
            intKTTruyVan = 0
        End Try
    End Sub

    Public Function ExecuteQuery(ByVal strSelect As String) As DataSet
        Dim dataset As New DataSet
        Try
            MoKetNoi()
            adapter = New MySqlDataAdapter(strSelect, connection)

            adapter.Fill(dataset)

            intKTTruyVan = 1
            DongKetNoi()
        Catch e As MySqlException
            intKTTruyVan = 0
        End Try
        Return DataSet
    End Function

    Public Function ExecuteQuery_Table(ByVal strSelect As String) As DataTable
        Dim table As DataTable
        Try
            table = ExecuteQuery(strSelect).Tables(0)
        Catch ex As Exception

        End Try
        Return table
    End Function

    Public Function ExecuteReader(ByVal strSelect As String) As DataTable

        Dim dataReader As MySqlDataReader
        Dim dataTable As DataTable
        Try
            MoKetNoi()
            command = New MySqlCommand(strSelect, connection)
            dataReader = command.ExecuteReader()
            dataTable = dataReader.GetSchemaTable()
            'While dataReader.Read()
            '    For i = 0 To dataReader.FieldCount - 1

            '        dataTable.Rows(0)(i) = dataReader(i)

            '    Next
            'End While
            DongKetNoi()
            intKTTruyVan = 1
        Catch ex As Exception
            intKTTruyVan = 0
        End Try

        Return dataTable
    End Function

    Public Function TaoMa(ByVal strID As String, ByVal field As String, ByVal table As String, ByVal length As Integer) As String
        Dim IDCode As String = strID & "0000000000"
        Try
            MoKetNoi()

            'select max(`qlbh`.`nhanvien`.`maNV`) from  `qlbh`.`nhanvien` where left(`maNV`,2)= 'NV';
            Dim query As String = "select max(`qlbh`.`" & table.Trim() & "`.`" & field.Trim() & "`) " & _
                                    "from `qlbh`.`" & table.Trim() & "` where left(`" & field.Trim() & "`," & strID.Length.ToString() & ")='" & strID.Trim() & "';"

            command = New MySqlCommand(query, connection)
            command.CommandType = CommandType.Text
            Dim reader As MySqlDataReader = command.ExecuteReader()

            While reader.Read()
                If Not reader.IsDBNull(0) Then
                    IDCode = Convert.ToString(reader.GetValue(0))
                End If
            End While

            IDCode = IDCode.Substring(strID.Length)
            IDCode = "0000000000000000" & Convert.ToString(Convert.ToInt64(IDCode) + 1)
            IDCode = strID + IDCode.Substring(IDCode.Length - length + strID.Length)

            DongKetNoi()
            intKTTruyVan = 1
            Return IDCode
        Catch ex As Exception
            intKTTruyVan = 0
        End Try
        Return IDCode
    End Function
End Class

